package com.hdc.walnut.common.utils.CRUD;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

@Component
public class DataQuery {
    @Autowired
    private MysqlUtils mysqlUtils;

    public ArrayList<Map<String,Object>> tableQuery(String tabName, String titles){
        Connection connection = mysqlUtils.getConnection();
        ArrayList<Map<String,Object>> paramAll = new ArrayList<Map<String,Object>>();
        Statement stmt =null;
        ResultSet rs =null;
        try{
            stmt = connection.createStatement();
            String sqlStrTab=String.format("SELECT * FROM  %s  %s",tabName,titles);
            System.out.println("[SQL]:"+sqlStrTab);
            rs = stmt.executeQuery(sqlStrTab);
            String sqlStrCol=String.format("SELECT distinct COLUMN_NAME  FROM information_schema.columns WHERE table_name='%s'",tabName);
            Statement stmt2 = connection.createStatement();
            while(rs.next()){
                Map <String,Object> param = new HashMap<String,Object>();
                ResultSet rsc = stmt2.executeQuery(sqlStrCol);
                while(rsc.next()){
                    String value = (String) rs.getString(rsc.getString("COLUMN_NAME"));
                    param.put((rsc.getString("COLUMN_NAME")).trim(),value);
                }
                paramAll.add(param);
            }
        }catch(SQLException e){
            throw new RuntimeException(e.getMessage());
        }finally {
            mysqlUtils.release(connection,stmt,rs);
        }
        return paramAll;
    }

    public Map<String,Object>  formQuery(String tabName,String titles){
        Connection connection = mysqlUtils.getConnection();
        Map <String,Object> param = new HashMap<String,Object>();
        Statement stmt =null;
        ResultSet rs = null;
        try{
            stmt = connection.createStatement();
            String sqlStrTab=String.format("SELECT * FROM  %s  %s",tabName,titles);
            System.out.println("[SQL]:"+sqlStrTab);
            rs = stmt.executeQuery(sqlStrTab);
            String sqlStrCol=String.format("SELECT  DISTINCT COLUMN_NAME  FROM information_schema.columns WHERE table_name='%s'",tabName);
            Statement stmt2 = connection.createStatement();
            while(rs.next()){
                ResultSet rsc = stmt2.executeQuery(sqlStrCol);
                while(rsc.next()){
                    String value = rs.getString(rsc.getString("COLUMN_NAME"));
                    param.put((rsc.getString("COLUMN_NAME")).trim(), value);
                }
            }
        }catch(SQLException e){
            throw  new  RuntimeException(e.getMessage());
        }finally {
            mysqlUtils.release(connection,stmt,rs);
        }
        return param;
    }
}
